USE [RLZY]
GO

/****** Object:  StoredProcedure [dbo].[pro_SQB_Unit1]    Script Date: 08/01/2014 16:28:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create proc [dbo].[pro_SQB_Unit1](@lastdate varchar(20),@thisdate varchar(20),@bm varchar(20))
as
if  exists (select name from sysobjects where name='GZ_SQB')  drop table GZ_SQB 
select [ID],[用工性质], [人数],  [技能工资], [岗位工资], [绩效工资],[三项补贴],[回贴],[工龄津贴],[职称津贴],[特岗津贴], [卫生纸费],[中晚班费],[加班工资],[补发],[扣事假工资],  [扣病假工资],[其它扣款], [应发总额] into GZ_SQB from GZ where substring(日期,1,10)='2012-04-01' 
delete GZ_SQB

declare  @VarX2 int,@VarX3 int, @VarX4 int, @VarX5 int,  @VarX6 Decimal(18,1),@VarX7 int,  @VarX8 int,   @VarX9 int,  @VarX10 int,  @VarX11 int,  @VarX12 Decimal(18,1), @VarX13 Decimal(18,1),@VarX14 Decimal(18,2),  @VarX15 Decimal(18,1),@VarX16 Decimal(18,1),@VarX17 Decimal(18,2),  @VarX18  Decimal(18,2) 
select  @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where 部门单位  like '%'+ @bm +'%' and (用工性质 like '%固%' or 用工性质 like '%制%' or 用工性质 like '%合%') and  substring(日期,1,7)=@thisdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('本月--合计',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18) 
select  @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where 部门单位  like '%'+ @bm +'%'  and 用工性质 like '%固%' and  substring(日期,1,7)=@thisdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('本月--固',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18)
select   @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where 部门单位  like '%'+ @bm +'%'  and 用工性质 like '%制%' and  substring(日期,1,7)=@thisdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('本月--制',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18) 
select   @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where 部门单位  like '%'+ @bm +'%'  and 用工性质 like '%合%' and  substring(日期,1,7)=@thisdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('本月--合',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18) 


GO

